import pandas as pd
from app import DrawUtil
from pyecharts.charts import Page

def read_amount():
    file_path = "./2023/amount.xlsx"
    df = pd.read_excel(file_path)
    # df_copy = df[['CLIENT_ID']]
    # df_copy.to_csv('2023/client_id.csv', index=False)
    return df

def read_client_assets():
    file_path = "./2023/client_assets.xlsx"
    df = pd.read_excel(file_path)
    return df

def read_client_assets_sum():
    file_path = "./2023/client_assets_sum.xlsx"
    df = pd.read_excel(file_path)
    return df

def read_grid_count():
    file_path = "./2023/grid_count.xlsx"
    return pd.read_excel(file_path)

def read_grid_year_info():
    file_path = "./2023/grid_year.csv"
    return pd.read_csv(file_path)

def read_grid_month_info():
    file_path = "./2023/grid_amount_month.xlsx"
    return pd.read_excel(file_path)

def read_grid_profit():
    file_path = "./2023/grid_profit.xlsx"
    return pd.read_excel(file_path)

def draw_result(df_merge, df_year, df_month):
    date_list = range(1, df_merge.shape[0])
    date_str_list = [str(value) for value in date_list]
    # print(date_str_list)

    amount_list = df_merge["amount"].tolist()
    client_id_list = df_merge["client_id"].tolist()
    asset_list = df_merge["asset"].tolist()
    grid_count = df_merge["count"].tolist()
    fare_list = df_merge["open_fare_ratex"].tolist()
    profit_list = df_merge["profit"].tolist()
    asset_avg_list = df_merge["asset_avg"].tolist()
    
    year_list = df_year["year"].tolist()
    year_user_list = df_year["user"].tolist()
    year_count_list = df_year["count"].tolist()
    year_amount_list = df_year["amount"].tolist()
    year_jdb_user = df_year["jdb_user"].tolist()
    year_jdb_amount = (df_year["jdb_amount"]).tolist()
    
    month_list = df_month["month"].tolist()
    month_amount_list = df_month["amount"].tolist()
    
    page = Page()

    money_line = DrawUtil.draw_line_with_multiple(date_str_list, {"客户网格(万)":amount_list}, "成交额(前100)", "300px")
    money_exclude13_line = DrawUtil.draw_line_with_multiple(date_str_list[12:], {"客户网格(万)":amount_list[12:]}, "成交额(排除头部)", "300px")
    page.add(
        money_line,
        money_exclude13_line
    )
    
    bar = DrawUtil.draw_bar_with_multiple(client_id_list, {"网格成交额(万)":amount_list, "网格收益(万)":profit_list, "日均总资产(万)":asset_avg_list, "网格数量":grid_count, "佣金比例(/万)":fare_list}, "客户画像", "300px")
    page.add(
        bar
    )

    bar_month = DrawUtil.draw_bar_with_multiple(month_list, {"网格成交额(万)":month_amount_list}, "月统计", "300px")

    bar_year = DrawUtil.draw_bar_with_multiple(year_list, {"网格用户数":year_user_list, "网格数量":year_count_list, "网格成交额(万)":year_amount_list,"九点半用户":year_jdb_user, "九点半成交额(亿)":year_jdb_amount}, "年统计", "300px")
    page.add(
        bar_month,
        bar_year
    )
    page.render("{}.html".format("网格数据分析"))

if __name__ == '__main__':
    # 网格成交额
    df_amount = read_amount()
    df_amount.columns = ['client_id', 'amount']
    # 网格用户总资产
    df_assets = read_client_assets()
    df_assets["asset"] = (df_assets["asset_td"] / 10000).round(3)
    # print(df_assets)
    df_merge = pd.merge(df_amount, df_assets, on='client_id', how='inner')
    
    df_assets_sum = read_client_assets_sum()
    df_assets_sum.columns = ["client_id", "asset_sum"]
    df_assets_sum["asset_avg"] = (df_assets_sum["asset_sum"]/(242 * 10000)).round(3)
    df_merge = pd.merge(df_merge, df_assets_sum, on='client_id', how='left').fillna(0)
    # 网格数量
    df_grid_count = read_grid_count()
    df_grid_count.columns = ['client_id', 'count']
    df_merge = pd.merge(df_merge, df_grid_count, on='client_id', how='left').fillna(0)
    # 网格收益数据
    df_profit = read_grid_profit()
    df_profit.columns = ['client_id', 'profit_old']
    df_profit['profit'] = (df_profit['profit_old']/10000).round(2)
    df_merge = pd.merge(df_merge, df_profit, on='client_id', how='left').fillna(0)
    # print(df_profit)
    df_merge.to_csv('2023/info_all.csv', index=False)
    df_merge = df_merge.sort_values(by='amount', ascending=False)
    # rows_to_keep = int(df_merge.shape[0] * 0.13)
    # df_merge = df_merge.iloc[rows_to_keep:]
    print(df_merge)

    # 年统计
    df_year = read_grid_year_info()

    df_month = read_grid_month_info()
    df_month.columns = ['month','amount']
    df_month_sort=df_month.sort_values(by='month')
    
    draw_result(df_merge, df_year, df_month_sort)
